from typing import Optional, List, Tuple
from sqlalchemy.orm import Session
from sqlalchemy import func, literal_column

from app.models.iaro_career.scholars import Scholar
from app.models.iaro_career.ite_detail import ITEDetail
from app.models.iaro_career.ite_supervisor import ITESupervisor
from app.models.iaro_career.personal_eval import PersonalEvaluationResponse
from app.models.iaro_coop_admin.sponsor import Sponsor, SponsorGroup, SponsorGroupList

class ScholarReportRepo:
    def __init__(self, db: Session):
        self.db = db

    def check_cocode_exists(self, cocode: str) -> bool:
        exists = (
            self.db.query(ITEDetail)
            .filter(ITEDetail.cocode == cocode)
            .first()
        )
        return exists is not None

    def get_cocodes_from_query(self, query: str) -> Tuple[List[str], bool]:
        group = (
            self.db.query(SponsorGroupList)
            .filter(SponsorGroupList.sponsor_group_code == query)
            .first()
        )
        if group:
            cocode_rows = (
                self.db.query(Sponsor.sponsor_code)
                .join(SponsorGroup, Sponsor.sponsor_id == SponsorGroup.sponsor_id)
                .filter(SponsorGroup.sponsor_group_id == group.sponsor_group_id)
                .all()
            )
            return [r.sponsor_code for r in cocode_rows], True
        else:
            return [query], False

    def get_performance_report_records(self, cocodes: Optional[List[str]]) -> List[dict]:
        grade_subq = (
            self.db.query(
                PersonalEvaluationResponse.scholar_id.label("scholar_id"),
                func.max(PersonalEvaluationResponse.ans_response).label("grade")
            )
            .filter(PersonalEvaluationResponse.question_id == 121)
            .group_by(PersonalEvaluationResponse.scholar_id)
        ).subquery()

        value_subq = (
            self.db.query(
                PersonalEvaluationResponse.scholar_id.label("scholar_id"),
                func.max(PersonalEvaluationResponse.ans_response).label("value_contributed")
            )
            .filter(PersonalEvaluationResponse.question_id == 122)
            .group_by(PersonalEvaluationResponse.scholar_id)
        ).subquery()

        feedback_subq = (
            self.db.query(
                PersonalEvaluationResponse.scholar_id.label("scholar_id"),
                func.max(PersonalEvaluationResponse.ans_response).label("feedback")
            )
            .filter(PersonalEvaluationResponse.question_id == 123)
            .group_by(PersonalEvaluationResponse.scholar_id)
        ).subquery()

        q = (
            self.db.query(
                ITEDetail.cocode.label("COCODE"),
                (Scholar.first_name + literal_column("' '") + Scholar.last_name).label("SCHOLARNAME"),
                ITEDetail.department.label("TEAM"),
                ITESupervisor.supervisor_name.label("SUPERVISOR"),
                grade_subq.c.grade.label("GRADE"),
                value_subq.c.value_contributed.label("VALUE CONTRIBUTED"),
                feedback_subq.c.feedback.label("FEEDBACK")
            )
            .join(ITEDetail, Scholar.scholar_id == ITEDetail.scholar_id)
            .outerjoin(ITESupervisor, ITESupervisor.ite_id == ITEDetail.ite_id)
            .outerjoin(grade_subq, grade_subq.c.scholar_id == Scholar.scholar_id)
            .outerjoin(value_subq, value_subq.c.scholar_id == Scholar.scholar_id)
            .outerjoin(feedback_subq, feedback_subq.c.scholar_id == Scholar.scholar_id)
        )

        if cocodes:
            q = q.filter(ITEDetail.cocode.in_(cocodes))

        results = q.all()

        GRADE_MAP = {
            10: "A+",
            9: "A",
            8: "A-",
            7: "B+",
            6: "B",
            5: "B-",
            4: "C+",
            3: "C",
            2: "C-",
            1: "D",
            0: "E"
        }

        VALUE_MAP = {
            0: "$0",
            1: "$1-5K",
            2: "$6-10K",
            3: "$11-20K",
            4: "$21-30K",
            5: "$31-40K",
            6: "N/A"
        }

        def format_grade(val):
            try:
                return GRADE_MAP.get(int(val), str(val))
            except:
                return str(val)

        def format_value(val):
            try:
                return VALUE_MAP.get(int(val), "Other")
            except:
                return "Other"

        return [
            {
                "COCODE": r[0], 
                "SCHOLAR NAME": r[1],
                "TEAM": r[2],
                "SUPERVISOR": r[3],
                "GRADE": format_grade(r[4]),
                "VALUE CONTRIBUTED": format_value(r[5]),
                "FEEDBACK": r[6]
            }
            for r in results
        ]
